package com.njcky.utils.db;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;

import java.sql.*;
import java.util.*;


@Slf4j
public class DBHelper {

//    public static void main(String[] args) {
//        System.out.println(NpgNormal_GetListDicNo1("select * from function","jdbc:postgresql://10.10.10.76:5432/yj_one_map","postgres","jsjm123.0"));
//    }

    //返回查询结果的(第一行)第一列
    public static Object NpgNormal_GetScalar(String sql, String pgUrl, String pgUserName, String pgPassword) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Object value = 0;
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //连接数据库
        try {
            connection = DriverManager.getConnection(pgUrl, pgUserName, pgPassword);
            //向数据库发送SQL的Statement
            statement = connection.createStatement();
            //执行sql，返回结果集
            resultSet = statement.executeQuery(sql);
            ResultSetMetaData md = resultSet.getMetaData();
            int columnCount = md.getColumnCount();
            int count = 0;
            while (resultSet.next()) {
                count++;
                for (int i = 1; i <= columnCount; i++) {
                    if (count == 1) {
                        value = resultSet.getObject(i);
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return value;
    }

    //返回查询结果集
    public static List<Map<String, Object>> NpgNormal_GetListDic(String sql, String pgUrl, String pgUserName, String pgPassword) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<Map<String, Object>> resultlist = new ArrayList<>();
        try {
            // 1. 连接耗时（已有统计）
            Class.forName("org.postgresql.Driver");
            connection = DriverManager.getConnection(pgUrl, pgUserName, pgPassword);
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql); // 此步骤仅触发SQL执行，不立即获取全部结果
            ResultSetMetaData md = resultSet.getMetaData();
            int columnCount = md.getColumnCount();
            while (resultSet.next()) { // 逐行获取结果，触发网络传输
                Map<String, Object> dic = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = md.getColumnLabel(i);
                    Object value = resultSet.getObject(i);
                    // 重点：空间字段shapetext的转换可能耗时（字符串可能很长）
                    dic.put(columnName, value == null ? "" : value.toString());
                }
                resultlist.add(dic);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源（略）
        }
        return resultlist;
    }



    public static List<Map<String, Object>> NpgNormal_GetListDic_getBsmDateParallel(String sql, String pgUrl, String pgUserName, String pgPassword) {
        Connection connection = null;
        PreparedStatement pstmt = null; // 使用JDBC预编译Statement
        ResultSet resultSet = null;
        List<Map<String, Object>> resultlist = new ArrayList<>();
        try {
            // 1. 加载驱动并建立连接
            Class.forName("org.postgresql.Driver");
            long connectStart = System.currentTimeMillis();
            connection = DriverManager.getConnection(pgUrl, pgUserName, pgPassword);
            long connectCost = System.currentTimeMillis() - connectStart;
            System.out.printf("连接建立耗时: %d ms%n", connectCost);

            // 2. 使用JDBC预编译（自动处理PREPARE逻辑）
            long sqlExecuteStart = System.currentTimeMillis();
            pstmt = connection.prepareStatement(sql); // 驱动自动预编译SQL
            resultSet = pstmt.executeQuery(); // 执行预编译后的查询

            // 计算SQL执行耗时
            long sqlExecuteCost = System.currentTimeMillis() - sqlExecuteStart;
            System.out.printf("123123数据库执行SQL耗时（纯执行，不含传输）: %d ms%n", sqlExecuteCost);

            // 3. 处理结果集
            long resultHandleStart = System.currentTimeMillis();
            ResultSetMetaData md = resultSet.getMetaData();
            int columnCount = md.getColumnCount();
            while (resultSet.next()) {
                Map<String, Object> dic = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = md.getColumnLabel(i);
                    Object value = resultSet.getObject(i);
                    dic.put(columnName, value == null ? "" : value.toString());
                }
                resultlist.add(dic);
            }
            long resultHandleCost = System.currentTimeMillis() - resultHandleStart;
            System.out.printf("结果集传输+处理耗时: %d ms%n", resultHandleCost);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (resultSet != null) resultSet.close();
                if (pstmt != null) pstmt.close(); // 关闭预编译Statement
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultlist;
    }


    // TODO 优化参数结构添加带参数的重载方法
    public static List<Map<String, Object>> NpgNormal_GetListDic(String sql, String pgUrl, String pgUserName, String pgPassword, Object... params) {
        return NpgNormal_GetListDicWithParams(sql, pgUrl, pgUserName, pgPassword, Arrays.asList(params));
    }

    // 核心参数化查询实现
    public static List<Map<String, Object>> NpgNormal_GetListDicWithParams(String sql, String pgUrl, String pgUserName, String pgPassword, List<Object> params) {
        Connection connection = null;
        PreparedStatement preparedStatement = null; // 使用PreparedStatement替代Statement
        ResultSet resultSet = null;
        List<Map<String, Object>> resultlist = new ArrayList<Map<String, Object>>();
        try {
            Class.forName("org.postgresql.Driver");
            // 连接数据库
            connection = DriverManager.getConnection(pgUrl, pgUserName, pgPassword);
            // 使用PreparedStatement预处理SQL，防止注入并提高效率
            log.info("SQL::{}",sql);
            preparedStatement = connection.prepareStatement(sql);

            // 设置参数
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    // PreparedStatement参数索引从1开始
                    preparedStatement.setObject(i + 1, params.get(i));
                }
            }

            // 执行查询
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData md = resultSet.getMetaData();
            int columnCount = md.getColumnCount();

            while (resultSet.next()) {
                Map<String, Object> dic = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    Object value = resultSet.getObject(i);
                    // 处理null值，转为空字符串
                    dic.put(md.getColumnLabel(i), value == null ? "" : value.toString());
                }
                resultlist.add(dic);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源，使用try-with-resources或工具类会更优雅
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultlist;
    }





    //返回查询第一行
    public static Map<String, Object> NpgNormal_GetListDicNo1(String sql, String pgUrl, String pgUserName, String pgPassword){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Map<String, Object> resultlist = new HashMap<>();
        try {
            Class.forName("org.postgresql.Driver");
            //连接数据库
            connection = DriverManager.getConnection(pgUrl, pgUserName, pgPassword);
            //向数据库发送SQL的Statement
            statement = connection.createStatement();
            resultSet = null;
            //执行sql，返回结果集
            resultSet = statement.executeQuery(sql);
            ResultSetMetaData md = resultSet.getMetaData();
            int columnCount = md.getColumnCount();
            if((resultSet.next())) {
                Map<String, Object> dic = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    resultlist.put(md.getColumnLabel(i), resultSet.getObject(i) == null ? "" : resultSet.getObject(i).toString());
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultlist;
    }

    //返回受影响行数 新增、修改、删除等操作
    public static int NpgNormal_ExecutorNonQuery(String sql, String pgUrl, String pgUserName, String pgPassword){
        int affectrows = 0;
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("org.postgresql.Driver");
            //连接数据库
            connection = DriverManager.getConnection(pgUrl, pgUserName, pgPassword);
            //向数据库发送SQL的Statement
            statement = connection.createStatement();
            affectrows=statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return affectrows;
    }


    /**
     * 获取总数
     */

    public static int NpgNormal_QueryCount(String sql, String pgUrl, String pgUserName, String pgPassword) {
        int total = 0;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null; // 用于接收查询结果

        try {
            Class.forName("org.postgresql.Driver");
            connection = DriverManager.getConnection(pgUrl, pgUserName, pgPassword);
            statement = connection.prepareStatement(sql);

            // 执行查询语句（SELECT用executeQuery）
            resultSet = statement.executeQuery();

            // 解析结果集（COUNT查询只有一条结果）
            if (resultSet.next()) {
                total = resultSet.getInt("total"); // 读取别名"total"对应的计数
            }
        } catch (Exception e) {
            e.printStackTrace();
            return 0; // 异常时返回0
        } finally {
            // 关闭资源（ResultSet也需要关闭）
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return total;
    }






}
